﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.ICM.ViewModel;
using KPIS.GERP.Models.SYS;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.ServiceModel;
using System.Web.Mvc;

namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IOfficeSuppliesLotService
    {
        [OperationContract]
        IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SelectSuppliesLot(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int InsertPicture(OfficeSuppliesLot m, int updateByUserId);

        [OperationContract]
        IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SuppliesLotByResourceItemCode(int ResItemId);

        [OperationContract]
        List<int> SuppliesLotQuantity(int ResItemId);

        [OperationContract]
        IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SelectSuppliesLotDetail(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        List<ReportOfficeSuppliesLot> ReportSuppliesLot(int ResGrpId, int BgSourceId, bool NotZero = false, DateTime? stockDate = null);

        [OperationContract]
        List<ReportOfficeSuppliesLot2> Report4SuppliesLot(DateTime StartDate, DateTime EndDate,  int bgSourceId);

        [OperationContract]
        ReportOfficeSuppliesLot2 Report4SuppliesLotPrev(DateTime StartDate, int ResItemId, int bgSourceId);

    }

    [ServiceErrorBehavior(typeof(ElmahErrorHandler))]
    public class OfficeSuppliesLotService : IOfficeSuppliesLotService
    {
        public IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SelectSuppliesLot(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<OfficeSuppliesLot> query = context.OfficeSuppliesLots.Include("Warehouse");

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SelectSuppliesLotDetail(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<OfficeSuppliesLot> query = context.OfficeSuppliesLots.Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup");

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertPicture(OfficeSuppliesLot model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();

                    return model.OfficeSuppliesLotId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<KPIS.GERP.Models.ICM.OfficeSuppliesLot> SuppliesLotByResourceItemCode(int ResItemId)
        {
            var context = new GERPContext(false);
            try
            {
                //var model = (from a in context.OfficeSuppliesLots.Include("Warehouse").Include("MasResourceItem").Include("MasUnit").Include("ProjectExpense").Include("ReceiveDtl").Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup")
                //             join b in context.MasResourceItems on a.ResItemId equals b.ResItemId
                //             where b.ResItemId == ResItemId
                //             select a);

                var model = from a in context.OfficeSuppliesLots.Include("Warehouse").Include("ProjectExpense.Project")
                            where a.ResItemId == ResItemId
                            select a;

                //var testQ = context.OfficeSuppliesLots.Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup");
                //var includeSubObjectString = new[] { "MasResourceItem", "MasUnit", "ProjectExpense", "ReceiveDtl", "Warehouse" };
                //var testM = includeSubObjectString.Aggregate(testQ, (current, includePath) => current.Include(includePath));
                //var testMR = testM.ToList();

                //var testQ2 = context.OfficeSuppliesLots.Include("Warehouse").Include("MasResourceItem").Include("MasUnit").Include("ProjectExpense").Include("ReceiveDtl").Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup");
                //var testMR2 = testQ2.ToList();

                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<int> SuppliesLotQuantity(int ResItemId)
        {
            var SumSuppliesLotQuantity = new List<int>();
            var context = new GERPContext(false);
            try
            {
                SumSuppliesLotQuantity = (from a in context.OfficeSuppliesLots
                                          join b in context.MasResourceItems on a.ResItemId equals b.ResItemId
                                          where b.ResItemId == ResItemId
                                          group a by new { a.OfficeSuppliesLotId } into g
                                          select g.Sum(a => a.Quantity)).ToList();

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return SumSuppliesLotQuantity;

        }

        public List<ReportOfficeSuppliesLot> ReportSuppliesLot(int ResGrpId, int BgSourceId, bool NotZero = false, DateTime? stockDate = null)
        {
            if (stockDate.HasValue && stockDate.Value.Year > 2400)
            {
                stockDate = stockDate.Value.AddYears(-543);
            }
            using (GERPContext context = new GERPContext())
            {
                DateTime? stockDate2 = !stockDate.HasValue ? (DateTime?)null :stockDate.Value.Date/*.AddDays(1)*/.AddSeconds(-1);
               
                var query = context.OfficeSuppliesLots
                    .Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup")
                    .Include("MasUnit")
                    .Include("ReceiveDtl.Received")
                    .Where(m => m.RecordStatus != "D")
                    .ToList();

                query = query.Where(m=> (ResGrpId == 0 || 
                        m.MasResourceItem.MasResourceSubGroup.ResGrpId == ResGrpId)) .ToList();

                if (BgSourceId != 0)
                {
                    if (BgSourceId != -1)
                    {
                        query = query.Where(m => m.BgSourceId == BgSourceId) .ToList();
                    }
                    else
                    { 
                        query = query.Where(m => m.BgSourceId == null) .ToList();
                    }
                }

                if (stockDate2 != null)
                { 
                    query = query
                        // .Where(m => m.ReceiveDtl.Received.ReceiveDate <= stockDate2)
                        .Where(m => m.CreatedWhen <= stockDate2)
                        .OrderBy(m => m.ResItemId)
                        .ToList(); 
                }
                
                var models = query.ToList(); 

                var queryFull = context.OfficeSuppliesLots
                    .Where(o=>o.RecordStatus != "D")
                    .Include("MasResourceItem.MasResourceSubGroup.MasResourceGroup")
                    .Include("MasUnit")
                    .OrderByDescending(m => m.OfficeSuppliesLotId);
                var modelsFull = queryFull.ToList();

                var resourceItems = context.MasResourceItems.Include(o=>o.MasUnit)
                    .Where(o => o.RecordStatus != "D").OrderBy(o => o.Code).ToList();
                var items = new List<ReportOfficeSuppliesLot>(); 
                foreach (var resourceItem in resourceItems)
                { 
                    bool foundAny = false;
                    foreach (var model in models.Where(o=>o.ResItemId == resourceItem.ResItemId))
                    {
                        var item = new ReportOfficeSuppliesLot();
                        item.Code = (model.MasResourceItem.Code??"").Trim();
                        item.Name = model.MasResourceItem.Name;
                        item.Price = model.Price;
                        item.Qty = model.Quantity;
                        item.Unit = model.MasUnit.Name;
                        item.Date = model.CreatedWhen;

                        //n++;
                        if (model.Quantity > 0)
                        {
                            items.Add(item);
                        }
                        if (foundAny != true && model.Quantity > 0)
                        {
                            foundAny = true;
                        }
                    }

                    // Get last before date start criteria
                    if (!NotZero && !foundAny)
                    {
                        var item = new ReportOfficeSuppliesLot();
                        item.Code = resourceItem.Code;
                        item.Name = resourceItem.Name;
                        item.Price = (from m in modelsFull where m.ResItemId == resourceItem.ResItemId select m.Price).FirstOrDefault();
                        item.Qty = (from m in modelsFull where m.ResItemId == resourceItem.ResItemId select m.Quantity).FirstOrDefault();
                        item.Unit = resourceItem.MasUnit != null ? resourceItem.MasUnit.Name : "";
                        item.Date = (from m in modelsFull where m.ResItemId == resourceItem.ResItemId select m.CreatedWhen).FirstOrDefault();
                        //n++;
                        items.Add(item);
                    }
                }
                return items.OrderBy(o => o.Code).ThenBy(o => o.Date).ToList();
            }
        }

        public List<ReportOfficeSuppliesLot2> Report4SuppliesLot(DateTime StartDate, DateTime EndDate, int bgSourceId)
        {
            StartDate = StartDate.Date;
            EndDate = EndDate.Date.AddDays(1).AddSeconds(-1);
            using (GERPContext context = new GERPContext())
            {
                // รับ
                /*
                var queryReceive = (from a in context.OfficeSuppliesLots
                                    join b in context.MasResourceItems on a.ResItemId equals b.ResItemId
                                    join c in context.MasResourceSubGroups on b.ResSubGrpId equals c.ResSubGrpId
                                    join d in context.MasResourceGroups on c.ResGrpId equals d.ResGrpId
                                    join e in context.MasResourceTypes on d.ResTypeId equals e.ResTypeId
                                    join f in context.ReceiveDtls on a.ReceiveDtlId equals f.ReceiveDtlId
                                    join g in context.Receiveds on f.ReceiveId equals g.ReceiveId
                                    //join h in context.MasUnits on a.UnitId equals h.UnitId
                                    where a.RecordStatus != "D" &&
                                    e.ResTypeId == 1 &&
                                    g.ReceiveDate >= StartDate &&
                                    g.ReceiveDate <= EndDate &&
                                    (bgSourceId == 0 ||
                                       (bgSourceId == -1 && a.BgSourceId == null) ||
                                       (bgSourceId != -1 && a.BgSourceId == bgSourceId)
                                       )
                                    select new
                                    {
                                        g.ReceiveDate,
                                        ReceiveCode = g.ReceiveCode != null && g.ReceiveCode != "" ? g.ReceiveCode : "RECEIVEID" + g.ReceiveId,
                                        f.Price,
                                        f.Quantity,
                                        ResItemId = b.ResItemId,
                                        a.BgSourceId,
                                        ResItemCode = b.Code,
                                        a.OfficeSuppliesLotId,
                                        f.ReceiveDtlId,
                                        g.ReceiveId
                                    });*/

                // รับ
                var queryReceiveD = (from a in context.OfficeSuppliesLots
                    join b in context.MasResourceItems on a.ResItemId equals b.ResItemId
                    join c in context.MasResourceSubGroups on b.ResSubGrpId equals c.ResSubGrpId
                    join d in context.MasResourceGroups on c.ResGrpId equals d.ResGrpId
                    join e in context.MasResourceTypes on d.ResTypeId equals e.ResTypeId
                    join f in context.ReceiveDtls on a.ReceiveDtlId equals f.ReceiveDtlId
                    join g in context.Receiveds on f.ReceiveId equals g.ReceiveId
                    //join h in context.MasUnits on a.UnitId equals h.UnitId
                    where a.RecordStatus != "D" && e.ResTypeId == 1 &&
                                    /*  g.ReceiveDate >= StartDate &&
                                    g.ReceiveDate <= EndDate &&*/
                                     a.CreatedWhen >= StartDate &&
                                    a.CreatedWhen <= EndDate 
                                    //&& b.Name.Contains("หนังสือ")
                    select new {a, b, c, d, e, f, g}).ToList();

                if (bgSourceId != 0)
                {
                    queryReceiveD = queryReceiveD.Where(o => o.a.BgSourceId == bgSourceId).ToList();
                }

                var queryReceive = queryReceiveD.Select(o=>new
                {
                    o.g.ReceiveDate,
                    ReceiveCode =
                        !string.IsNullOrEmpty(o.g.ReceiveCode) ? o.g.ReceiveCode : "RECEIVEID" + o.g.ReceiveId,
                    o.f.Price,
                    o.f.Quantity,
                    o.b.ResItemId,
                    o.a.BgSourceId,
                    ResItemCode = o.b.Code,
                    o.a.OfficeSuppliesLotId,
                    o.f.ReceiveDtlId,
                    o.g.ReceiveId
                });
                var models = queryReceiveD.ToList(); 
                // var test = queryReceive.Where(o=>o.)

                
                List<ReportOfficeSuppliesLot2> Dics = new List<ReportOfficeSuppliesLot2>();

                foreach (var receive in queryReceive)
                {
                    ReportOfficeSuppliesLot2 dic = new ReportOfficeSuppliesLot2();

                    dic.Amount = receive.Price * receive.Quantity;
                    dic.Qty = receive.Quantity;
                    dic.Price = receive.Price;
                    dic.ResItemCode = receive.ResItemCode;
                    dic.Code = receive.ReceiveCode;
                    dic.Date = receive.ReceiveDate;
                    dic.ResItemId = receive.ResItemId;
                    dic.Type = "R"; 
                    Dics.Add(dic);
                }

                // จ่าย
                var queryDispense = (from a in context.DispenseHistorys
                         join b in context.Dispenses.Include("RequestParcel") on a.DispenseId equals b.DispenseId
                        join c in context.MasResourceItems on a.ResItemId equals c.ResItemId
                         where b.DispenseDate >= StartDate && b.DispenseDate <= EndDate
                        select new { a.Price, a.Quantity, a.RefNo, b.DispenseDate, c.ResItemId, b.RequestParcel.IcmReqCode, ResItemCode = c.Code });

                foreach (var dispense in queryDispense)
                {
                    ReportOfficeSuppliesLot2 dic = new ReportOfficeSuppliesLot2();

                    dic.Price = Convert.ToDecimal(dispense.Price);
                    dic.Qty = Convert.ToDecimal(dispense.Quantity);
                    dic.ResItemCode = dispense.ResItemCode;
                    dic.Code = dispense.IcmReqCode + (string.IsNullOrWhiteSpace(dispense.RefNo) ? "" : "-" + dispense.RefNo);
                    dic.Amount = Convert.ToDecimal(dispense.Price * dispense.Quantity);
                    dic.Date = Convert.ToDateTime(dispense.DispenseDate);
                    dic.ResItemId = dispense.ResItemId;
                    dic.Type = "D";
                    Dics.Add(dic);
                }

                return Dics.OrderBy(m => m.ResItemCode).ToList();
            }
        }

        public ReportOfficeSuppliesLot2 Report4SuppliesLotPrev(DateTime StartDate, int ResItemId, int bgSourceId)
        {
            StartDate = StartDate.Date; 
            using (GERPContext context = new GERPContext())
            {
                // รับ
                var x = (from a in context.OfficeSuppliesLots
                         join b in context.MasResourceItems on a.ResItemId equals b.ResItemId
                         join c in context.MasResourceSubGroups on b.ResSubGrpId equals c.ResSubGrpId
                         join d in context.MasResourceGroups on c.ResGrpId equals d.ResGrpId
                         join e in context.MasResourceTypes on d.ResTypeId equals e.ResTypeId
                         join f in context.ReceiveDtls on a.ReceiveDtlId equals f.ReceiveDtlId
                         join g in context.Receiveds on f.ReceiveId equals g.ReceiveId
                         //join h in context.MasUnits on a.UnitId equals h.UnitId
                         where a.RecordStatus != "D" &&
                         a.ResItemId == ResItemId && 
                         // g.ReceiveDate < StartDate &&
                         a.CreatedWhen < StartDate &&
                         (bgSourceId == 0 || 
                            (bgSourceId == -1 && a.BgSourceId == null ||
                            bgSourceId != -1 && a.BgSourceId == bgSourceId )
                         )
                         select new
                         {
                             f.Quantity, f.Price
                         }).ToList();
                decimal dQtyReceive = x.Sum(m => m.Quantity);
                decimal dSumReceive = x.Sum(m => m.Price * m.Quantity);

                // จ่าย
                var y = (from a in context.DispenseHistorys
                         join b in context.Dispenses on a.DispenseId equals b.DispenseId
                         //join c in context.MasResourceItems on a.ResItemId equals c.ResItemId
                         where a.ResItemId == ResItemId && 
                         b.DispenseDate < StartDate 
                         select new 
                         { 
                             a.Price, a.Quantity
                         }).ToList();
                decimal dQtyDispense = y.Sum(m => m.Quantity ?? 0);
                decimal dSumDispense = y.Sum(m => (m.Price ?? 0) * (m.Quantity ?? 0) );

                // ยกมา
                var model = new ReportOfficeSuppliesLot2();

                model.ResItemId = ResItemId;
                model.Qty = dQtyReceive - dSumDispense;
                model.Amount = dSumReceive - dSumDispense;
                model.Price =  model.Qty != 0 ? model.Amount / model.Qty : 0;
                model.Type = "T";

                return model ;
            }
        }
    }
}
